package com.java.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import com.java.dao.UserDao;
import com.java.model.PageBean;
import com.java.model.User;
import com.java.util.StringUtil;

@Repository("userDao")
public class UserDaoImpl implements UserDao {

	@Resource
	private JdbcTemplate jdbcTemplate;

	@Override
	public User login(User user) {
		String sql = "select a.*, c.phone, c.email, c.address from t_user a left join t_user_courier b on a.id = b.userId left join t_courier c on b.courierId = c.id where userName=? and password=?";
		final User resultUser = new User();
		jdbcTemplate.query(sql, new Object[] { user.getUserName(), user.getPassword() }, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				resultUser.setId(rs.getInt("id"));
				resultUser.setUserName(rs.getString("userName"));
				resultUser.setPassword(rs.getString("password"));
				resultUser.setRoleName(rs.getString("roleName"));
				resultUser.setPhone(rs.getString("phone"));
				resultUser.setEmail(rs.getString("email"));
				resultUser.setAddress(rs.getString("address"));
				resultUser.setCompanyId(rs.getString("companyId"));
			}
		});
		return resultUser;
	}

	@Override
	public List<User> find(PageBean pageBean, User s_user) {
		StringBuffer sb = new StringBuffer("select * from t_user t1,t_department t2 where t1.deptId=t2.id");
		if (s_user != null) {
			if (StringUtil.isNotEmpty(s_user.getUserName())) {
				sb.append(" and t1.userName like '%" + s_user.getUserName() + "%'");
			}
		}
		if (pageBean != null) {
			sb.append(" limit " + pageBean.getStart() + "," + pageBean.getPageSize());
		}
		final List<User> userList = new ArrayList<User>();
		jdbcTemplate.query(sb.toString(), new Object[] {}, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password"));
				user.setTrueName(rs.getString("trueName"));
				user.setRoleName(rs.getString("roleName"));
				user.setDeptId(rs.getInt("deptId"));
				user.setDeptName(rs.getString("deptName"));
				user.setCompanyId(rs.getString("companyId"));
				userList.add(user);
			}
		});
		return userList;
	}

	@Override
	public int count(User s_user) {
		StringBuffer sb = new StringBuffer("select count(*) from t_user t1,t_department t2 where t1.deptId=t2.id");
		if (s_user != null) {
			if (StringUtil.isNotEmpty(s_user.getUserName())) {
				sb.append(" and userName like '%" + s_user.getUserName() + "%'");
			}
		}
		return jdbcTemplate.queryForObject(sb.toString(), Integer.class);
	}

	@Override
	public void delete(int id) {
		String sql = "delete from t_user where id=?";
		jdbcTemplate.update(sql, new Object[] { id });
	}

	@Override
	public void add(User user) {
		// 应该写在事物中
		String sql = "insert into t_user values(null,?,?,?,?,?,?)";
		jdbcTemplate.update(sql, new Object[] { user.getUserName(), user.getPassword(), user.getTrueName(),
				user.getRoleName(), user.getDeptId(),user.getCompanyId() });
		int userId = jdbcTemplate.queryForInt("SELECT LAST_INSERT_ID()");
		String sql2 = "insert into t_courier values(null,?,?,?)";
		jdbcTemplate.update(sql2, new Object[] { user.getPhone(), user.getEmail(), user.getAddress() });
		int courierId = jdbcTemplate.queryForInt("SELECT LAST_INSERT_ID()");
		System.out.println("add:userId" + userId + ",courierId:" + courierId);
		String sql3 = "insert into t_user_courier values(null,?,?)";
		jdbcTemplate.update(sql3, new Object[] { userId, courierId });
	}

	@Override
	public void update(User user) {
		System.out.println(user.toString());
		String sql = "update t_user set userName=?,password=?,trueName=?,roleName=?,deptId=? where id=?";
		jdbcTemplate.update(sql, new Object[] { user.getUserName(), user.getPassword(), user.getTrueName(),
				user.getRoleName(), user.getDeptId(), user.getId() });
	}

	@Override
	public User loadById(int id) {
		String sql = "select * from t_user t1,t_department t2 where t1.deptId=t2.id and t1.id=?";

		final User user = new User();
		jdbcTemplate.query(sql, new Object[] { id }, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password"));
				user.setTrueName(rs.getString("trueName"));
				user.setRoleName(rs.getString("roleName"));
				user.setDeptId(rs.getInt("deptId"));
				user.setDeptName(rs.getString("deptName"));
				user.setCompanyId(rs.getString("companyId"));
			}
		});
		return user;
	}

	@Override
	public boolean existUserByDeptId(int deptId) {
		String sql = "select count(*) from t_user where deptId=?";
		int result = jdbcTemplate.queryForObject(sql, new Object[] { deptId }, Integer.class);
		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean existUserName(String userName) {
		String sql = "select count(*) from t_user where userName=?";
		int result = jdbcTemplate.queryForObject(sql, new Object[] { userName }, Integer.class);
		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}
}
